package com.ggb.reactive.service

import com.ggb.reactive.domain.dto.GoodsSalesDTO
import com.ggb.reactive.domain.dto.OrdersPageQueryDTO
import com.ggb.reactive.domain.entity.Orders
import com.ggb.reactive.model.PageResult
import lombok.RequiredArgsConstructor
import org.springframework.data.domain.PageImpl
import org.springframework.data.domain.PageRequest
import org.springframework.data.r2dbc.core.R2dbcEntityTemplate
import org.springframework.data.relational.core.query.Criteria
import org.springframework.data.relational.core.query.Query
import org.springframework.r2dbc.core.DatabaseClient
import org.springframework.stereotype.Service
import reactor.core.publisher.Flux
import reactor.core.publisher.Mono
import java.math.BigDecimal
import java.sql.Timestamp
import java.time.Instant
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

@Service
@RequiredArgsConstructor
class OrderService(
    private val r2dbcEntityTemplate: R2dbcEntityTemplate,
    private val databaseClient: DatabaseClient
) {

    /**
     * 计算订单的营业额
     */
    fun sumByMap(status: Int?, begin: LocalDateTime?, end: LocalDateTime?): Mono<Double> {
        val sql = StringBuilder("select sum(amount)  from orders where 1=1")
        status?.let {
            sql.append(" and status = :status")
        }
        begin?.let {
            sql.append(" and order_time >= :begin")
        }
        end?.let {
            sql.append(" and order_time <= :end")
        }


        var spec = databaseClient.sql(sql.toString())

        status?.let {
            spec = spec.bind("status", it)
        }
        begin?.let {
            spec = spec.bind("begin", it)
        }
        end?.let {
            spec = spec.bind("end", it)
        }

        return spec.map { row -> row.get("sum(amount)", Double::class.java) }
            .one()
            .map { sum -> sum ?: 0.0 } // 如果 sum 为 null，则返回 0.0
            .onErrorResume {
                Mono.just(0.0) // 返回一个默认值，避免终止流程
            }

    }

    /**
     * 计算订单的数量统计
     */
    fun countByMap(status: Int?, begin: LocalDateTime?, end: LocalDateTime?): Mono<Int> {
        val sql = StringBuilder("select count(id) from orders where 1=1")

        status?.let {
            sql.append(" and status = :status")
        }
        begin?.let {
            sql.append(" and order_time >= :begin")
        }
        end?.let {
            sql.append(" and order_time <= :end")
        }


        var spec = databaseClient.sql(sql.toString())

        status?.let {
            spec = spec.bind("status", it)
        }
        begin?.let {
            spec = spec.bind("begin", it.toString())
        }
        end?.let {
            spec = spec.bind("end", it.toString())
        }

        return spec.map { row -> row.get("count(id)", Int::class.java) }
            .one()
            .map { sum -> sum ?: 0 } // 如果 sum 为 null，则返回 0.0
            .onErrorResume {
                Mono.just(0) // 返回一个默认值，避免终止流程
            }
    }

    /**
     * 计算订单的数量统计
     */
    fun getSalesTop10(begin: LocalDateTime?, end: LocalDateTime?): Flux<GoodsSalesDTO> {
        val sql =
            StringBuilder("SELECT od.name as name, SUM(od.number) AS number FROM order_detail od, orders o WHERE od.order_id = o.id AND o.status = 5")

        begin?.let {
            sql.append(" AND o.order_time >= :begin")
        }
        end?.let {
            sql.append(" AND o.order_time <= :end")
        }
        sql.append(" GROUP BY od.name ORDER BY number DESC LIMIT 10")

        var spec = databaseClient.sql(sql.toString())
        begin?.let {
            spec = spec.bind("begin", begin)
        }
        end?.let {
            spec = spec.bind("end", end)
        }
        return spec.map { row ->
            GoodsSalesDTO(
                name = row.get("name", String::class.java),
                number = row.get("number", Int::class.java)
            )
        }
            .all()
    }

    /**
     * 根据状态查询订单
     */
    fun getByStatusAndOrderTimLT(status: Int, orderTime: Instant): Flux<Orders> {
        val sql = "select * from orders  where status = :status and order_time < :orderTime"
        return databaseClient.sql(sql)
            .bind(0, status)
            .bind(1, orderTime)
            .map { row ->
                Orders(
                    id = row.get("id", Long::class.java)!!,
                    number = row.get("number", String::class.java),
                    status = row.get("status", Int::class.java),
                    userId = row.get("user_id", Long::class.java),
                    addressBookId = row.get("address_book_id", Long::class.java),
                    payStatus = row.get("pay_status", Int::class.java),
                    payMethod = row.get("pay_method", Int::class.java),
                    orderTime = row.get("order_time", Instant::class.java),
                    checkoutTime = row.get("checkout_time", Instant::class.java),
                    amount = row.get("amount", BigDecimal::class.java),
                    remark = row.get("remark", String::class.java),
                    userName = row.get("user_name", String::class.java),
                    phone = row.get("phone", String::class.java),
                    address = row.get("address", String::class.java),
                    consignee = row.get("consignee", String::class.java),
                    cancelReason = row.get("cancel_reason", String::class.java),
                    rejectionReason = row.get("rejection_reason", String::class.java),
                    cancelTime = row.get("cancel_time", Instant::class.java),
                    estimatedDeliveryTime = row.get("estimated_delivery_time", Instant::class.java),
                    deliveryStatus = row.get("delivery_status", Int::class.java),
                    deliveryTime = row.get("delivery_time", Instant::class.java),
                    packAmount = row.get("pack_amount", Int::class.java),
                    tablewareNumber = row.get("tableware_number", Int::class.java),
                    tablewareStatus = row.get("tableware_status", Int::class.java)
                )
            }
            .all()
    }


    fun findOrdersByCriteria(ordersPageQueryDTO: OrdersPageQueryDTO): Mono<PageResult<Orders>> {
        var criteria = Criteria.empty()

        ordersPageQueryDTO.number?.let {
            criteria = criteria.and("number").like("%$it%")
        }
        ordersPageQueryDTO.status?.let {
            criteria = criteria.and("status").`is`(it)
        }
        ordersPageQueryDTO.phone?.let {
            criteria = criteria.and("phone").`is`(it)
        }
        ordersPageQueryDTO.userId?.let {
            criteria = criteria.and("user_id").`is`(it)
        }

        ordersPageQueryDTO.beginTime?.let {
            criteria = criteria.and("order_time").greaterThanOrEquals(it)
        }
        ordersPageQueryDTO.endTime?.let {
            criteria = criteria.and("order_time").lessThanOrEquals(it)
        }

        val pageable = PageRequest.of(ordersPageQueryDTO.page - 1, ordersPageQueryDTO.pageSize)


        val query = Query.query(criteria).with(pageable)
        val countQuery = Query.query(criteria)

        return r2dbcEntityTemplate.select(query, Orders::class.java).collectList()
            .zipWith(r2dbcEntityTemplate.count(countQuery, Orders::class.java))
            // todo 需要查一步 获取口味字符串
            .map { tuple -> PageImpl(tuple.t1, pageable, tuple.t2) }
            .map { page ->
                // 将Page<User>转换为你的PageResult<User>（假设你有一个类似的结构）
                PageResult(total = page.totalElements, records = page.content)
            }

    }


    /**
     * 更新订单
     */
    fun updateOrder(order: Orders): Mono<Long> {
        val updates = listOfNotNull(
            order.cancelReason?.let { "cancel_reason = :cancelReason" to it },
            order.rejectionReason?.let { "rejection_reason = :rejectionReason" to it },
            order.cancelTime?.let { "cancel_time = :cancelTime" to it },
            order.payStatus?.let { "pay_status = :payStatus" to it },
            order.payMethod?.let { "pay_method = :payMethod" to it },
            order.status?.let { "status = :status" to it },
            order.checkoutTime?.let { "checkout_time = :checkoutTime" to it },
            order.deliveryTime?.let { "delivery_time = :deliveryTime" to it }
        )

        if (updates.isEmpty() || order.id == null) {
            return Mono.just(0L)
        }

        val setClause = updates.joinToString(", ") { it.first }
        val sql = "UPDATE orders SET $setClause WHERE id = :id"

        var updateSpec = databaseClient.sql(sql)
            .bind("id", order.id!!)

        updates.forEach { (key, value) ->
            // Here we correct the split part to properly extract the named parameter from the SQL snippet
            val paramName = key.substringAfter(" = :")
            updateSpec = updateSpec.bind(paramName, value)
        }

        return updateSpec.fetch().rowsUpdated()
    }


}